package com.example.agriapp.data;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

import com.example.agriapp.model.ProductContract;
import com.example.agriapp.R;
import com.example.agriapp.model.FeedContract;
import com.example.agriapp.model.bean.FeedItem;
import com.example.agriapp.model.bean.Message;
import com.example.agriapp.model.bean.Product;
import com.example.agriapp.model.bean.User;
import com.example.agriapp.view.fragment.MessageFragment;

import java.util.ArrayList;
import java.util.List;

public class AgriDatabaseHelper extends SQLiteOpenHelper {
    public static final String COLUMN_ID = "id";
    // 数据库信息
    private static final String DATABASE_NAME = "agri_database.db";
    private static final int DATABASE_VERSION = 3; // 版本号升级

    // 用户表信息
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_USER_ID = "user_id";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_REAL_NAME = "real_name";
    public static final String COLUMN_PHONE = "phone";

    // 创建用户表SQL
    private static final String SQL_CREATE_USERS_TABLE =
            "CREATE TABLE " + TABLE_USERS + " (" +
                    COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USERNAME + " TEXT NOT NULL UNIQUE, " +
                    COLUMN_PASSWORD + " TEXT NOT NULL, " +
                    COLUMN_REAL_NAME + " TEXT, " +
                    COLUMN_PHONE + " TEXT NOT NULL UNIQUE" +
                    ");";

    // 产品表信息
    public static final String TABLE_PRODUCTS = "products";
    public static final String COLUMN_PRODUCT_ID = "id";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_PRICE = "price";
    public static final String COLUMN_QUANTITY = "quantity";
    public static final String COLUMN_CATEGORY = "category";
    public static final String COLUMN_IMAGE = "image";
    public static final String COLUMN_VIEW_COUNT = "view_count";
    // 创建产品表SQL
    private static final String SQL_CREATE_PRODUCTS_TABLE =
            "CREATE TABLE " + TABLE_PRODUCTS + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_TITLE + " TEXT NOT NULL, " +
                    COLUMN_PRICE + " REAL NOT NULL, " +
                    COLUMN_QUANTITY + " INTEGER NOT NULL DEFAULT 0, " +
                    COLUMN_CATEGORY + " TEXT, " +
                    COLUMN_IMAGE + " TEXT, " +
                    COLUMN_VIEW_COUNT + " INTEGER DEFAULT 0" +  // 新增浏览量字段
                    ");";
    private static final String SQL_CREATE_FEEDS_TABLE = "CREATE TABLE " +
            FeedContract.FeedEntry.TABLE_NAME + " (" +
            FeedContract.FeedEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            FeedContract.FeedEntry.COLUMN_USER_ID + " TEXT NOT NULL, " +
            FeedContract.FeedEntry.COLUMN_USERNAME + " TEXT NOT NULL, " +
            FeedContract.FeedEntry.COLUMN_AVATAR + " TEXT, " +
            FeedContract.FeedEntry.COLUMN_CONTENT + " TEXT NOT NULL, " +
            FeedContract.FeedEntry.COLUMN_DATE + " TEXT NOT NULL, " +
            FeedContract.FeedEntry.COLUMN_LIKES + " INTEGER DEFAULT 0, " +
            FeedContract.FeedEntry.COLUMN_COMMENTS + " INTEGER DEFAULT 0, " +
            FeedContract.FeedEntry.COLUMN_HAS_SHOP + " INTEGER DEFAULT 0, " +
            FeedContract.FeedEntry.COLUMN_IS_FOLLOWED + " INTEGER DEFAULT 0, " +
            FeedContract.FeedEntry.COLUMN_TAG + " TEXT, " +
            FeedContract.FeedEntry.COLUMN_IMAGE_URI + " TEXT);";

    // 在AgriDatabaseHelper类中添加以下常量定义

    // 浏览记录表信息
    public static final String TABLE_BROWSING_HISTORY = "browsing_history";
    public static final String COLUMN_BROWSING_ID = "browsing_id";
    public static final String COLUMN_BROWSING_TIME = "browsing_time";

    // 创建浏览记录表SQL
    private static final String SQL_CREATE_BROWSING_HISTORY_TABLE =
            "CREATE TABLE " + TABLE_BROWSING_HISTORY + " (" +
                    COLUMN_BROWSING_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL, " +
                    COLUMN_PRODUCT_ID + " INTEGER NOT NULL, " +
                    COLUMN_BROWSING_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " +
                    TABLE_USERS + "(" + COLUMN_USER_ID + "), " +
                    "FOREIGN KEY(" + COLUMN_PRODUCT_ID + ") REFERENCES " +
                    TABLE_PRODUCTS + "(" + COLUMN_PRODUCT_ID + ")" +
                    ");";

    // 在AgriDatabaseHelper类中添加以下常量定义

    // 收藏表信息
    public static final String TABLE_FAVORITES = "favorites";
    public static final String COLUMN_FAVORITE_ID = "favorite_id";
    public static final String COLUMN_FAVORITE_TIME = "favorite_time";

    // 创建收藏表SQL
    private static final String SQL_CREATE_FAVORITES_TABLE =
            "CREATE TABLE " + TABLE_FAVORITES + " (" +
                    COLUMN_FAVORITE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL, " +
                    COLUMN_PRODUCT_ID + " INTEGER NOT NULL, " +
                    COLUMN_FAVORITE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " +
                    TABLE_USERS + "(" + COLUMN_USER_ID + "), " +
                    "FOREIGN KEY(" + COLUMN_PRODUCT_ID + ") REFERENCES " +
                    TABLE_PRODUCTS + "(" + COLUMN_PRODUCT_ID + "), " +
                    "UNIQUE(" + COLUMN_USER_ID + ", " + COLUMN_PRODUCT_ID + ")" +
                    ");";


    // 采购表信息
    public static final String TABLE_PURCHASES = "purchases";
    public static final String COLUMN_PURCHASE_ID = "purchase_id";
    public static final String COLUMN_PURCHASE_TITLE = "purchase_title";
    public static final String COLUMN_PURCHASE_DESC = "purchase_desc";
    public static final String COLUMN_PURCHASE_CATEGORY = "purchase_category";
    public static final String COLUMN_PURCHASE_QUANTITY = "purchase_quantity";
    public static final String COLUMN_PURCHASE_PRICE = "purchase_price";
    public static final String COLUMN_PURCHASE_TIME = "purchase_time";
    public static final String COLUMN_PURCHASE_IMAGE = "purchase_image";

    // 创建采购表SQL
    private static final String SQL_CREATE_PURCHASES_TABLE =
            "CREATE TABLE " + TABLE_PURCHASES + " (" +
                    COLUMN_PURCHASE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL, " +
                    COLUMN_PURCHASE_TITLE + " TEXT NOT NULL, " +
                    COLUMN_PURCHASE_DESC + " TEXT, " +
                    COLUMN_PURCHASE_CATEGORY + " TEXT, " +
                    COLUMN_PURCHASE_IMAGE + " TEXT, " +
                    COLUMN_PURCHASE_QUANTITY + " INTEGER DEFAULT 1, " +
                    COLUMN_PURCHASE_PRICE + " REAL, " +
                    COLUMN_PURCHASE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " +
                    TABLE_USERS + "(" + COLUMN_USER_ID + ")" +
                    ");";
    private Context context;

    public AgriDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建产品表
        db.execSQL(SQL_CREATE_PRODUCTS_TABLE);

        // 创建动态表
        db.execSQL(SQL_CREATE_FEEDS_TABLE);

        // 创建用户表
        db.execSQL(SQL_CREATE_USERS_TABLE);
        db.execSQL(SQL_CREATE_CART_TABLE);
        db.execSQL(SQL_CREATE_ORDERS_TABLE);
        db.execSQL(SQL_CREATE_BROWSING_HISTORY_TABLE);
        db.execSQL(SQL_CREATE_FAVORITES_TABLE);
        db.execSQL(SQL_CREATE_PURCHASES_TABLE);
        db.execSQL(SQL_CREATE_MESSAGES_TABLE);
        // 插入初始数据
        insertInitialFeedData(db);
        insertTestProduct(db, "新鲜苹果", 5.99, 100, "水果蔬菜", R.drawable.apple);
        insertTestProduct(db, "草莓", 3.49, 80, "水果蔬菜", R.drawable.strawberry);
        insertTestProduct(db, "优质大米", 12.99, 50, "米面粮油", R.drawable.rice);
        insertTestProduct(db, "散养鸡蛋", 8.99, 120, "米面粮油", R.drawable.eggs);
        insertTestProduct(db, "粉条", 8.99, 120, "米面粮油", R.drawable.sweet_potato_noodles);
        insertTestProduct(db, "蜂蜜", 6.50, 60, "米面粮油", R.drawable.honey);
        insertTestProduct(db, "锄头", 6.50, 60, "农资农具", R.drawable.img_1);

        // 插入测试消息数据
        insertTestMessage(db, 1, "张三", "你好，这是一条测试消息1");
        insertTestMessage(db, 2, "李四", "请问这个产品还有货吗？");
        insertTestMessage(db, 1, "王五", "明天可以发货吗？");

    }

    private void insertTestMessage(SQLiteDatabase db, int userId, String username, String content) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_MESSAGE_CONTENT, content);
        db.insert(TABLE_MESSAGES, null, values);
    }

    public void insertTestProduct(SQLiteDatabase db,
                                  String title, double price, int quantity,
                                  String category, int imageResId) {
        ContentValues values = new ContentValues();
        values.put(ProductContract.ProductEntry.COLUMN_TITLE, title);
        values.put(ProductContract.ProductEntry.COLUMN_PRICE, price);
        values.put(ProductContract.ProductEntry.COLUMN_QUANTITY, quantity);
        values.put(ProductContract.ProductEntry.COLUMN_CATEGORY, category);

        values.put(ProductContract.ProductEntry.COLUMN_IMAGE, Uri.parse("android.resource://" + context.getPackageName() + "/" + imageResId).toString());

        db.insert(TABLE_PRODUCTS, null, values);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
        db.execSQL("DROP TABLE IF EXISTS " + FeedContract.FeedEntry.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CART);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BROWSING_HISTORY);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVORITES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGES);
        db.execSQL(SQL_CREATE_PURCHASES_TABLE);
        onCreate(db);
    }

    private void insertInitialFeedData(SQLiteDatabase db) {
        // 第一条动态
        ContentValues values1 = new ContentValues();
        values1.put(FeedContract.FeedEntry.COLUMN_USER_ID, "user_1533361");
        values1.put(FeedContract.FeedEntry.COLUMN_USERNAME, "153****3361");
        values1.put(FeedContract.FeedEntry.COLUMN_AVATAR, "default_avatar");
        values1.put(FeedContract.FeedEntry.COLUMN_CONTENT, "大家好");
        values1.put(FeedContract.FeedEntry.COLUMN_DATE, "2024-05-19");
        values1.put(FeedContract.FeedEntry.COLUMN_HAS_SHOP, 1);
        db.insert(FeedContract.FeedEntry.TABLE_NAME, null, values1);

        // 第二条动态
        ContentValues values2 = new ContentValues();
        values2.put(FeedContract.FeedEntry.COLUMN_USER_ID, "user_shang");
        values2.put(FeedContract.FeedEntry.COLUMN_USERNAME, "商丘市菜篮子");
        values2.put(FeedContract.FeedEntry.COLUMN_AVATAR, "shangqiu_avatar");
        values2.put(FeedContract.FeedEntry.COLUMN_CONTENT,
                "商丘月老红缘相亲节目每个周六周日准时与你邂逅");
        values2.put(FeedContract.FeedEntry.COLUMN_DATE, "2024-05-18");
        values2.put(FeedContract.FeedEntry.COLUMN_LIKES, 1);
        values2.put(FeedContract.FeedEntry.COLUMN_HAS_SHOP, 1);
        values2.put(FeedContract.FeedEntry.COLUMN_TAG, "月老红缘");
        values2.put(FeedContract.FeedEntry.COLUMN_IMAGE_URI, "content://media/poster.jpg");
        db.insert(FeedContract.FeedEntry.TABLE_NAME, null, values2);

        // 第三条动态
        ContentValues values3 = new ContentValues();
        values3.put(FeedContract.FeedEntry.COLUMN_USER_ID, "user_shangqiu");
        values3.put(FeedContract.FeedEntry.COLUMN_USERNAME, "商丘市菜篮子");
        values3.put(FeedContract.FeedEntry.COLUMN_AVATAR, "shangqiu_avatar");
        values3.put(FeedContract.FeedEntry.COLUMN_CONTENT, "美丽的商丘古城");
        values3.put(FeedContract.FeedEntry.COLUMN_DATE, "2024-05-10");
        values3.put(FeedContract.FeedEntry.COLUMN_HAS_SHOP, 1);
        values3.put(FeedContract.FeedEntry.COLUMN_IMAGE_URI, "content://media/city.jpg");
        db.insert(FeedContract.FeedEntry.TABLE_NAME, null, values3);
    }

    /**
     * 验证用户是否存在
     *
     * @param username 用户名
     * @return 用户是否存在
     */
    public boolean isUserExists(String username) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_USERS +
                " WHERE " + COLUMN_USERNAME + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{username});
        boolean exists = cursor.getCount() > 0;
        cursor.close();
        return exists;
    }

    /**
     * 注册新用户
     *
     * @param username 用户名
     * @param password 密码
     * @param realName 真实姓名
     * @param phone    手机号
     * @return 注册是否成功
     */
    public boolean registerUser(String username, String password,
                                String realName, String phone) {
        if (isUserExists(username)) {
            return false;
        }

        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_PASSWORD, password);
        values.put(COLUMN_REAL_NAME, realName);
        values.put(COLUMN_PHONE, phone);

        long result = db.insert(TABLE_USERS, null, values);
        return result != -1;
    }

    /**
     * 验证用户登录
     *
     * @param username 用户名
     * @param password 密码
     * @return 用户ID，-1表示验证失败
     */
    public int validateUser(String username, String password) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT " + COLUMN_USER_ID + " FROM " + TABLE_USERS +
                " WHERE " + COLUMN_USERNAME + " = ? AND " +
                COLUMN_PASSWORD + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{username, password});

        if (cursor.moveToFirst()) {
            int userId = cursor.getInt(0);
            cursor.close();
            return userId;
        }
        cursor.close();
        return -1;
    }

    /**
     * 根据用户ID获取用户信息
     *
     * @param userId 用户ID
     * @return User对象，null表示未找到
     */
    public User getUserInfo(int userId) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_USERS +
                " WHERE " + COLUMN_USER_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});

        if (cursor.moveToFirst()) {
            User user = new User(
                    cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_USERNAME)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_REAL_NAME)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_PHONE))
            );
            cursor.close();
            return user;
        }
        cursor.close();
        return null;
    }

    /**
     * 根据用户名获取用户信息
     *
     * @param username 用户名
     * @return User对象，null表示未找到
     */
    public User getUserInfo(String username) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_USERS +
                " WHERE " + COLUMN_USERNAME + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{username});

        if (cursor.moveToFirst()) {
            User user = new User(
                    cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_USERNAME)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_REAL_NAME)),
                    cursor.getString(cursor.getColumnIndex(COLUMN_PHONE))
            );
            cursor.close();
            return user;
        }
        cursor.close();
        return null;
    }


    // 消息表信息
    public static final String TABLE_MESSAGES = "messages";
    public static final String COLUMN_MESSAGE_ID = "message_id";
    public static final String COLUMN_MESSAGE_CONTENT = "message_content";
    public static final String COLUMN_MESSAGE_TIME = "message_time";

    // 创建消息表SQL
    private static final String SQL_CREATE_MESSAGES_TABLE =
            "CREATE TABLE " + TABLE_MESSAGES + " (" +
                    COLUMN_MESSAGE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL, " +
                    COLUMN_USERNAME + " TEXT NOT NULL, " +
                    COLUMN_MESSAGE_CONTENT + " TEXT NOT NULL, " +
                    COLUMN_MESSAGE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " +
                    TABLE_USERS + "(" + COLUMN_USER_ID + ")" +
                    ");";

    // 购物车表信息
    public static final String TABLE_CART = "cart";
    public static final String COLUMN_CART_ID = "cart_id";
    public static final String COLUMN_CART_QUANTITY = "cart_quantity";

    // 创建购物车表SQL
    private static final String SQL_CREATE_CART_TABLE =
            "CREATE TABLE " + TABLE_CART + " (" +
                    COLUMN_CART_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL, " +
                    COLUMN_PRODUCT_ID + " INTEGER NOT NULL, " +
                    COLUMN_CART_QUANTITY + " INTEGER DEFAULT 1, " +
                    "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " +
                    TABLE_USERS + "(" + COLUMN_USER_ID + "), " +
                    "FOREIGN KEY(" + COLUMN_PRODUCT_ID + ") REFERENCES " +
                    TABLE_PRODUCTS + "(" + COLUMN_PRODUCT_ID + ")" +
                    ");";


    // 订单表信息
    public static final String TABLE_ORDERS = "orders";
    public static final String COLUMN_ORDER_ID = "order_id";
    public static final String COLUMN_ORDER_TIME = "order_time";
    public static final String COLUMN_STATUS = "status";

    // 创建订单表SQL
    private static final String SQL_CREATE_ORDERS_TABLE =
            "CREATE TABLE " + TABLE_ORDERS + " (" +
                    COLUMN_ORDER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL, " +
                    COLUMN_PRODUCT_ID + " INTEGER NOT NULL, " +
                    COLUMN_QUANTITY + " INTEGER DEFAULT 1, " +
                    COLUMN_ORDER_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    COLUMN_STATUS + " INTEGER DEFAULT 0, " + // 0-待支付 1-已支付 2-已取消
                    "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " +
                    TABLE_USERS + "(" + COLUMN_USER_ID + "), " +
                    "FOREIGN KEY(" + COLUMN_PRODUCT_ID + ") REFERENCES " +
                    TABLE_PRODUCTS + "(" + COLUMN_PRODUCT_ID + ")" +
                    ");";

    /**
     * 立即购买
     */
    public boolean createOrder(int userId, int productId, int quantity) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_PRODUCT_ID, productId);
        values.put(COLUMN_QUANTITY, quantity);
        values.put(COLUMN_STATUS, 1); // 默认待支付状态

        long result = db.insert(TABLE_ORDERS, null, values);
        return result != -1;
    }

    /**
     * 获取用户订单列表
     */
    public List<Product> getOrders(int userId) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT p.*, o." + COLUMN_QUANTITY + ", o." + COLUMN_STATUS +
                " FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_ORDERS + " o " +
                "ON p." + COLUMN_ID + " = o." + COLUMN_PRODUCT_ID + " " +
                "WHERE o." + COLUMN_USER_ID + " = ? " +
                "ORDER BY o." + COLUMN_ORDER_TIME + " DESC";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});
        List<Product> orderList = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                // 如果需要，可以设置收藏时间
                // product.setExtraInfo("favorite_time", cursor.getString(cursor.getColumnIndex(COLUMN_FAVORITE_TIME)));

                orderList.add(product);
            }
        } finally {
            cursor.close();
        }

        return orderList;
    }

    /**
     * 获取用户购物车列表
     */
    public List<Product> getCartItems(int userId) {
        List<Product> cartItems = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT p.*, c." + COLUMN_CART_QUANTITY +
                " FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_CART + " c " +
                "ON p." + COLUMN_ID + " = c." + COLUMN_PRODUCT_ID + " " +
                "WHERE c." + COLUMN_USER_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});
        // 获取购物车产品列表
        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                // 获取产品信息
                int productId = cursor.getInt(cursor.getColumnIndex(AgriDatabaseHelper.COLUMN_PRODUCT_ID));
                product.setId(productId);
                String title = cursor.getString(cursor.getColumnIndex(AgriDatabaseHelper.COLUMN_TITLE));
                product.setTitle(title);
                double price = cursor.getDouble(cursor.getColumnIndex(AgriDatabaseHelper.COLUMN_PRICE));
                product.setPrice(price);
                int quantity = cursor.getInt(cursor.getColumnIndex(AgriDatabaseHelper.COLUMN_CART_QUANTITY));
                product.setQuantity(quantity);
                String category = cursor.getString(cursor.getColumnIndex(AgriDatabaseHelper.COLUMN_CATEGORY));
                product.setCategory(category);
                String image = cursor.getString(cursor.getColumnIndex(AgriDatabaseHelper.COLUMN_IMAGE));
                product.setImage(image);
                // 处理产品信息...
                cartItems.add(product);
            }
        } finally {
            cursor.close();
        }// 记得关闭Cursor
        return cartItems;
    }

    /**
     * 添加商品到购物车
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @param quantity  数量
     * @return 是否添加成功
     */
    public boolean addToCart(int userId, int productId, int quantity) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_PRODUCT_ID, productId);
        values.put(COLUMN_CART_QUANTITY, quantity);

        // 先检查是否已存在相同商品
        String query = "SELECT * FROM " + TABLE_CART +
                " WHERE " + COLUMN_USER_ID + " = ? AND " +
                COLUMN_PRODUCT_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId), String.valueOf(productId)});

        if (cursor.getCount() > 0) {
            // 如果已存在，则更新数量
            cursor.moveToFirst();
            int currentQty = cursor.getInt(cursor.getColumnIndex(COLUMN_CART_QUANTITY));
            cursor.close();
            return db.update(TABLE_CART, values,
                    COLUMN_USER_ID + " = ? AND " + COLUMN_PRODUCT_ID + " = ?",
                    new String[]{String.valueOf(userId), String.valueOf(productId)}) > 0;
        } else {
            // 不存在则新增记录
            cursor.close();
            long result = db.insert(TABLE_CART, null, values);
            return result != -1;
        }
    }

    /**
     * 设置产品收藏状态
     */
    public boolean setFavorite(int productId, boolean isFavorite) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("is_favorite", isFavorite ? 1 : 0);

        int rows = db.update(TABLE_PRODUCTS, values,
                COLUMN_ID + " = ?", new String[]{String.valueOf(productId)});
        return rows > 0;
    }

    /**
     * 添加浏览记录（同时增加产品浏览量）
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 是否添加成功
     */
    public boolean addBrowsingHistory(int userId, int productId) {
        SQLiteDatabase db = getWritableDatabase();

        // 先增加产品浏览量
        incrementViewCount(productId);

        // 原有浏览记录逻辑...
        String query = "SELECT * FROM " + TABLE_BROWSING_HISTORY +
                " WHERE " + COLUMN_USER_ID + " = ? AND " +
                COLUMN_PRODUCT_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId), String.valueOf(productId)});

        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_PRODUCT_ID, productId);

        boolean result;
        if (cursor.getCount() > 0) {
            result = db.update(TABLE_BROWSING_HISTORY, values,
                    COLUMN_USER_ID + " = ? AND " + COLUMN_PRODUCT_ID + " = ?",
                    new String[]{String.valueOf(userId), String.valueOf(productId)}) > 0;
        } else {
            result = db.insert(TABLE_BROWSING_HISTORY, null, values) != -1;
        }

        cursor.close();
        return result;
    }

    /**
     * 增加产品浏览量
     *
     * @param productId 产品ID
     * @return 是否更新成功
     */
    public boolean incrementViewCount(int productId) {
        SQLiteDatabase db = getWritableDatabase();

        // 使用原子操作增加浏览量
        String updateSql = "UPDATE " + TABLE_PRODUCTS +
                " SET " + COLUMN_VIEW_COUNT + " = " + COLUMN_VIEW_COUNT + " + 1" +
                " WHERE " + COLUMN_ID + " = ?";

        try {
            db.execSQL(updateSql, new Object[]{productId});
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 获取产品浏览量
     *
     * @param productId 产品ID
     * @return 浏览量，-1表示获取失败
     */
    public int getProductViewCount(int productId) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT " + COLUMN_VIEW_COUNT +
                " FROM " + TABLE_PRODUCTS +
                " WHERE " + COLUMN_ID + " = ?";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(productId)});

        try {
            if (cursor.moveToFirst()) {
                return cursor.getInt(0);
            }
            return -1;
        } finally {
            cursor.close();
        }
    }

    /**
     * 获取热门产品（按浏览量降序）
     *
     * @param limit 返回数量限制
     * @return 热门产品列表
     */
    public List<Product> getPopularProducts(int limit) {
        List<Product> popularProducts = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_PRODUCTS +
                " ORDER BY " + COLUMN_VIEW_COUNT + " DESC" +
                " LIMIT ?";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(limit)});

        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                popularProducts.add(product);
            }
        } finally {
            cursor.close();
        }

        return popularProducts;
    }

    /**
     * 获取用户浏览记录（按时间倒序）
     *
     * @param userId 用户ID
     * @return 浏览记录列表（包含产品信息）
     */
    public List<Product> getBrowsingHistory(int userId) {
        List<Product> historyList = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT p.*, h." + COLUMN_BROWSING_TIME +
                " FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_BROWSING_HISTORY + " h " +
                "ON p." + COLUMN_ID + " = h." + COLUMN_PRODUCT_ID + " " +
                "WHERE h." + COLUMN_USER_ID + " = ? " +
                "ORDER BY h." + COLUMN_BROWSING_TIME + " DESC";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});

        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                // 如果需要，可以设置浏览时间
                // product.setExtraInfo("browsing_time", cursor.getString(cursor.getColumnIndex(COLUMN_BROWSING_TIME)));

                historyList.add(product);
            }
        } finally {
            cursor.close();
        }

        return historyList;
    }

    /**
     * 删除单条浏览记录
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 是否删除成功
     */
    public boolean deleteBrowsingHistory(int userId, int productId) {
        SQLiteDatabase db = getWritableDatabase();
        int rows = db.delete(TABLE_BROWSING_HISTORY,
                COLUMN_USER_ID + " = ? AND " + COLUMN_PRODUCT_ID + " = ?",
                new String[]{String.valueOf(userId), String.valueOf(productId)});
        return rows > 0;
    }

    /**
     * 清空用户浏览记录
     *
     * @param userId 用户ID
     * @return 是否清空成功
     */
    public boolean clearBrowsingHistory(int userId) {
        SQLiteDatabase db = getWritableDatabase();
        int rows = db.delete(TABLE_BROWSING_HISTORY,
                COLUMN_USER_ID + " = ?",
                new String[]{String.valueOf(userId)});
        return rows > 0;
    }

    /**
     * 获取用户最近浏览的N条记录
     *
     * @param userId 用户ID
     * @param limit  记录条数限制
     * @return 浏览记录列表
     */
    public List<Product> getRecentBrowsingHistory(int userId, int limit) {
        List<Product> historyList = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT p.* FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_BROWSING_HISTORY + " h " +
                "ON p." + COLUMN_ID + " = h." + COLUMN_PRODUCT_ID + " " +
                "WHERE h." + COLUMN_USER_ID + " = ? " +
                "ORDER BY h." + COLUMN_BROWSING_TIME + " DESC " +
                "LIMIT ?";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId), String.valueOf(limit)});

        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                historyList.add(product);
            }
        } finally {
            cursor.close();
        }

        return historyList;
    }

    /**
     * 检查用户是否浏览过某产品
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 是否浏览过
     */
    public boolean hasBrowsedProduct(int userId, int productId) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT 1 FROM " + TABLE_BROWSING_HISTORY +
                " WHERE " + COLUMN_USER_ID + " = ? AND " +
                COLUMN_PRODUCT_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId), String.valueOf(productId)});
        boolean result = cursor.getCount() > 0;
        cursor.close();
        return result;
    }

    /**
     * 添加收藏
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 是否添加成功
     */
    public boolean addFavorite(int userId, int productId) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_PRODUCT_ID, productId);

        try {
            long result = db.insertWithOnConflict(TABLE_FAVORITES, null, values,
                    SQLiteDatabase.CONFLICT_IGNORE);
            return result != -1;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 移除收藏
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 是否移除成功
     */
    public boolean removeFavorite(int userId, int productId) {
        SQLiteDatabase db = getWritableDatabase();
        int rows = db.delete(TABLE_FAVORITES,
                COLUMN_USER_ID + " = ? AND " + COLUMN_PRODUCT_ID + " = ?",
                new String[]{String.valueOf(userId), String.valueOf(productId)});
        return rows > 0;
    }

    /**
     * 检查是否已收藏
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 是否已收藏
     */
    public boolean isFavorite(int userId, int productId) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT 1 FROM " + TABLE_FAVORITES +
                " WHERE " + COLUMN_USER_ID + " = ? AND " +
                COLUMN_PRODUCT_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId), String.valueOf(productId)});
        boolean result = cursor.getCount() > 0;
        Log.e("TAG", "isFavorite: " + result);
        cursor.close();
        return result;
    }

    /**
     * 获取用户收藏列表（按收藏时间倒序）
     *
     * @param userId 用户ID
     * @return 收藏产品列表
     */
    public List<Product> getFavorites(int userId) {
        List<Product> favoriteList = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT p.*, f." + COLUMN_FAVORITE_TIME +
                " FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_FAVORITES + " f " +
                "ON p." + COLUMN_ID + " = f." + COLUMN_PRODUCT_ID + " " +
                "WHERE f." + COLUMN_USER_ID + " = ? " +
                "ORDER BY f." + COLUMN_FAVORITE_TIME + " DESC";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});

        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                // 如果需要，可以设置收藏时间
                // product.setExtraInfo("favorite_time", cursor.getString(cursor.getColumnIndex(COLUMN_FAVORITE_TIME)));

                favoriteList.add(product);
            }
        } finally {
            cursor.close();
        }

        return favoriteList;
    }

    /**
     * 获取用户收藏数量
     *
     * @param userId 用户ID
     * @return 收藏数量
     */
    public int getFavoriteCount(int userId) {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT COUNT(*) FROM " + TABLE_FAVORITES +
                " WHERE " + COLUMN_USER_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});

        int count = 0;
        if (cursor.moveToFirst()) {
            count = cursor.getInt(0);
        }
        cursor.close();
        return count;
    }

    /**
     * 切换收藏状态
     *
     * @param userId    用户ID
     * @param productId 产品ID
     * @return 切换后的状态（true表示已收藏，false表示未收藏）
     */
    public boolean toggleFavorite(int userId, int productId) {
        if (isFavorite(userId, productId)) {
            removeFavorite(userId, productId);
            return false;
        } else {
            addFavorite(userId, productId);
            return true;
        }
    }

    /**
     * 清空用户收藏
     *
     * @param userId 用户ID
     * @return 是否清空成功
     */
    public boolean clearFavorites(int userId) {
        SQLiteDatabase db = getWritableDatabase();
        int rows = db.delete(TABLE_FAVORITES,
                COLUMN_USER_ID + " = ?",
                new String[]{String.valueOf(userId)});
        return rows > 0;
    }

    /**
     * 获取用户最近收藏的N条记录
     *
     * @param userId 用户ID
     * @param limit  记录条数限制
     * @return 收藏产品列表
     */
    public List<Product> getRecentFavorites(int userId, int limit) {
        List<Product> favoriteList = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT p.* FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_FAVORITES + " f " +
                "ON p." + COLUMN_ID + " = f." + COLUMN_PRODUCT_ID + " " +
                "WHERE f." + COLUMN_USER_ID + " = ? " +
                "ORDER BY f." + COLUMN_FAVORITE_TIME + " DESC " +
                "LIMIT ?";

        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId), String.valueOf(limit)});

        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                favoriteList.add(product);
            }
        } finally {
            cursor.close();
        }

        return favoriteList;
    }


    /**
     * 插入动态数据
     *
     * @param feedItem 动态数据对象
     * @return 是否插入成功
     */
    public boolean insertFeed(FeedItem feedItem) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(FeedContract.FeedEntry.COLUMN_USER_ID, feedItem.getUserId());
        values.put(FeedContract.FeedEntry.COLUMN_USERNAME, feedItem.getUsername());
        values.put(FeedContract.FeedEntry.COLUMN_AVATAR, feedItem.getAvatar());
        values.put(FeedContract.FeedEntry.COLUMN_CONTENT, feedItem.getContent());
        values.put(FeedContract.FeedEntry.COLUMN_DATE, feedItem.getDate());
        values.put(FeedContract.FeedEntry.COLUMN_LIKES, feedItem.getLikes());
        values.put(FeedContract.FeedEntry.COLUMN_COMMENTS, feedItem.getComments());
        values.put(FeedContract.FeedEntry.COLUMN_HAS_SHOP, feedItem.hasShop() ? 1 : 0);
        values.put(FeedContract.FeedEntry.COLUMN_IS_FOLLOWED, feedItem.isFollowed() ? 1 : 0);
        values.put(FeedContract.FeedEntry.COLUMN_TAG, feedItem.getTag());
        values.put(FeedContract.FeedEntry.COLUMN_IMAGE_URI, feedItem.getImageUri());

        long result = db.insert(FeedContract.FeedEntry.TABLE_NAME, null, values);
        return result != -1;
    }

// ... existing code ...

    /**
     * 获取所有订单列表
     *
     * @return 订单列表
     */
    public List<Product> getAllOrders() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT p.*, o." + COLUMN_QUANTITY + ", o." + COLUMN_STATUS +
                " FROM " + TABLE_PRODUCTS + " p " +
                "INNER JOIN " + TABLE_ORDERS + " o " +
                "ON p." + COLUMN_ID + " = o." + COLUMN_PRODUCT_ID + " " +
                "ORDER BY o." + COLUMN_ORDER_TIME + " DESC";

        Cursor cursor = db.rawQuery(query, null);
        List<Product> orderList = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                orderList.add(product);
            }
        } finally {
            cursor.close();
        }
        return orderList;
    }

    /**
     * 查询当日销售额
     *
     * @return 当日销售额
     */
    public double getTodaySales() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT SUM(p." + COLUMN_PRICE + " * o." + COLUMN_QUANTITY + ") " +
                "FROM " + TABLE_ORDERS + " o " +
                "INNER JOIN " + TABLE_PRODUCTS + " p ON o." + COLUMN_PRODUCT_ID + " = p." + COLUMN_ID + " " +
                "WHERE o." + COLUMN_STATUS + " = 1 " + // 已支付订单
                "AND date(o." + COLUMN_ORDER_TIME + ") = date('now')";
        Cursor cursor = db.rawQuery(query, null);

        double total = 0;
        if (cursor.moveToFirst()) {
            total = cursor.getDouble(0);
        }
        cursor.close();
        return total;
    }

    /**
     * 查询当月销售额
     *
     * @return 当月销售额
     */
    public double getMonthSales() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT SUM(p." + COLUMN_PRICE + " * o." + COLUMN_QUANTITY + ") " +
                "FROM " + TABLE_ORDERS + " o " +
                "INNER JOIN " + TABLE_PRODUCTS + " p ON o." + COLUMN_PRODUCT_ID + " = p." + COLUMN_ID + " " +
                "WHERE o." + COLUMN_STATUS + " = 1 " + // 已支付订单
                "AND strftime('%Y-%m', o." + COLUMN_ORDER_TIME + ") = strftime('%Y-%m', 'now')";
        Cursor cursor = db.rawQuery(query, null);

        double total = 0;
        if (cursor.moveToFirst()) {
            total = cursor.getDouble(0);
        }
        cursor.close();
        return total;
    }

    /**
     * 查询当年销售额
     *
     * @return 当年销售额
     */
    public double getYearSales() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT SUM(p." + COLUMN_PRICE + " * o." + COLUMN_QUANTITY + ") " +
                "FROM " + TABLE_ORDERS + " o " +
                "INNER JOIN " + TABLE_PRODUCTS + " p ON o." + COLUMN_PRODUCT_ID + " = p." + COLUMN_ID + " " +
                "WHERE o." + COLUMN_STATUS + " = 1 " + // 已支付订单
                "AND strftime('%Y', o." + COLUMN_ORDER_TIME + ") = strftime('%Y', 'now')";
        Cursor cursor = db.rawQuery(query, null);

        double total = 0;
        if (cursor.moveToFirst()) {
            total = cursor.getDouble(0);
        }
        cursor.close();
        return total;
    }

    // ... existing code ...

    /**
     * 获取总销售额（所有已支付订单）
     * @return 总销售额
     */
    public double getTotalSales() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT SUM(p." + COLUMN_PRICE + " * o." + COLUMN_QUANTITY + ") " +
                "FROM " + TABLE_ORDERS + " o " +
                "INNER JOIN " + TABLE_PRODUCTS + " p ON o." + COLUMN_PRODUCT_ID + " = p." + COLUMN_ID + " " +
                "WHERE o." + COLUMN_STATUS + " = 1"; // 只计算已支付订单

        Cursor cursor = db.rawQuery(query, null);
        double total = 0;
        if (cursor.moveToFirst()) {
            total = cursor.getDouble(0);
        }
        cursor.close();
        return total;
    }

// ... existing code ...
    /**
     * 获取所有产品列表
     *
     * @return 产品列表
     */
    public List<Product> getAllProducts() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_PRODUCTS + " ORDER BY " + COLUMN_ID + " DESC";
        Cursor cursor = db.rawQuery(query, null);

        List<Product> productList = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                product.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                product.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_QUANTITY)));
                product.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_CATEGORY)));
                product.setImage(cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE)));
                product.setViewCount(cursor.getInt(cursor.getColumnIndex(COLUMN_VIEW_COUNT)));

                productList.add(product);
            }
        } finally {
            cursor.close();
        }
        return productList;
    }

    /**
     * 发送消息
     */
    public boolean sendMessage(int userId, String username, String content) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_MESSAGE_CONTENT, content);

        long result = db.insert(TABLE_MESSAGES, null, values);
        return result != -1;
    }

    /**
     * 获取所有消息
     */
    public List<Message> getAllMessages() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_MESSAGES +
                " ORDER BY " + COLUMN_MESSAGE_TIME + " DESC";
        Cursor cursor = db.rawQuery(query, null);

        List<Message> messageList = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                Message message = new Message();
                message.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_MESSAGE_ID)));
                message.setUserId(cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)));
                message.setUsername(cursor.getString(cursor.getColumnIndex(COLUMN_USERNAME)));
                message.setContent(cursor.getString(cursor.getColumnIndex(COLUMN_MESSAGE_CONTENT)));
                message.setTime(cursor.getString(cursor.getColumnIndex(COLUMN_MESSAGE_TIME)));

                messageList.add(message);
            }
        } finally {
            cursor.close();
        }
        return messageList;
    }

// ... existing code ...

    /**
     * 添加采购记录
     */
    public boolean addPurchase(int userId, String title, String description,
                               String category, int quantity, double price, String imageUrl) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_PURCHASE_TITLE, title);
        values.put(COLUMN_PURCHASE_DESC, description);
        values.put(COLUMN_PURCHASE_CATEGORY, category);
        values.put(COLUMN_PURCHASE_QUANTITY, quantity);
        values.put(COLUMN_PURCHASE_PRICE, price);
        values.put(COLUMN_PURCHASE_IMAGE, imageUrl);

        long result = db.insert(TABLE_PURCHASES, null, values);
        return result != -1;
    }

    /**
     * 获取所有采购记录
     */
    public List<Purchase> getAllPurchases() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_PURCHASES +
                " ORDER BY " + COLUMN_PURCHASE_TIME + " DESC";
        Cursor cursor = db.rawQuery(query, null);

        List<Purchase> purchaseList = new ArrayList<>();
        try {
            while (cursor.moveToNext()) {
                Purchase purchase = new Purchase();
                purchase.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_PURCHASE_ID)));
                purchase.setUserId(cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)));
                purchase.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_PURCHASE_TITLE)));
                purchase.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_PURCHASE_DESC)));
                purchase.setCategory(cursor.getString(cursor.getColumnIndex(COLUMN_PURCHASE_CATEGORY)));
                purchase.setQuantity(cursor.getInt(cursor.getColumnIndex(COLUMN_PURCHASE_QUANTITY)));
                purchase.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PURCHASE_PRICE)));
                purchase.setTime(cursor.getString(cursor.getColumnIndex(COLUMN_PURCHASE_TIME)));
                purchase.setImageUrl(cursor.getString(cursor.getColumnIndex(COLUMN_PURCHASE_IMAGE)));

                purchaseList.add(purchase);
            }
        } finally {
            cursor.close();
        }
        return purchaseList;
    }
}
